package nus.iss.laps.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import nus.iss.laps.cri.LeaveCri;
import nus.iss.laps.general.LeaveStatus;
import nus.iss.laps.model.Leave;

public class LeaveDAO extends DAO implements ILeaveDAO, RowMapper<Leave> {

	public LeaveDAO()
	{
		table = "leaves";
	}
	
	@Override
	public boolean addLeave(Leave leave) throws SQLException {
		query = "insert into " + table + "(createddate, modifieddate, leavestatus, employeeid, leavecategory, startdate, enddate, dissemination, reason, days, supervisorid, comment) " 
				+ " values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		jdbcTemplate = new JdbcTemplate(dataSource);
		return jdbcTemplate.update(query, leave.getCreatedDate(), leave.getModifiedDate(), leave.getLeaveStatus(), leave.getEmployeeID(), leave.getLeaveCategory(), leave.getStartDate(), leave.getEndDate(), leave.getDissemination(), leave.getReason(), leave.getDays(), leave.getSupervisorID(), leave.getComment()) > 0;
	}

	@Override
	public boolean updateLeave(Leave leave) throws SQLException {
		query =  "update " + table + " set "
				+ "days = ?, "
				+ "dissemination = ?, "
				+ "endDate = ?, "
				+ "leaveCategory = ?, "
				+ "modifiedDate = ?, "
				+ "reason = ?, "
				+ "startDate = ?, "
				+ "supervisorID = ? "+
				"where leaveID = '" + leave.getLeaveID() + "'";
		jdbcTemplate = new JdbcTemplate(dataSource);
		return jdbcTemplate.update(query,leave.getDays(),leave.getDissemination(),leave.getEndDate(),
				leave.getLeaveCategory(),leave.getModifiedDate(),leave.getReason(),
				leave.getStartDate(),leave.getSupervisorID()) > 0;
	}

	@Override
	public boolean deleteLeave(long leaveID) throws SQLException {
		query = "update " + table + " set leavestatus = " + LeaveStatus.delete + " where leaveID = '" + leaveID + "'";
		jdbcTemplate = new JdbcTemplate(dataSource);
		return jdbcTemplate.update(query) > 0;
	}

	@Override
	public Leave getLeave(long leave) throws SQLException {
		try {
			try {
				query = getSelectQuery() + " where leaveID = '" + leave + "'";
				jdbcTemplate = new JdbcTemplate(dataSource);
			} catch (IncorrectResultSizeDataAccessException e) {
				return null;
			}
			return jdbcTemplate.queryForObject(query, this);
		} catch (IncorrectResultSizeDataAccessException e) {
			return null;
		}
	}

	@Override
	public List<Leave> getAllLeave() throws SQLException {
		query = getSelectQuery();
		jdbcTemplate = new JdbcTemplate(dataSource);
		return jdbcTemplate.query(query, this);
	}
	
	@Override
	public Leave mapRow(ResultSet arg0, int arg1) throws SQLException {
		Leave l_leave = new Leave();
		l_leave.setLeaveID(arg0.getLong("leaveID"));
		l_leave.setComment(arg0.getString("comment"));
		l_leave.setCreatedDate(arg0.getString("createdDate"));
		l_leave.setDays(arg0.getInt("days"));
		l_leave.setDissemination(arg0.getString("dissemination"));
		l_leave.setEmployeeID(arg0.getString("employeeID"));
		l_leave.setEndDate(arg0.getString("endDate"));
		l_leave.setLeaveCategory(arg0.getString("leaveCategory"));
		l_leave.setLeaveStatus(arg0.getByte("leaveStatus"));
        l_leave.setModifiedDate(arg0.getString("modifiedDate"));
        l_leave.setReason(arg0.getString("reason"));
        l_leave.setStartDate(arg0.getString("startDate"));
        l_leave.setSupervisorID(arg0.getString("supervisorID"));
		
	      return l_leave;
	}
	
	public List<Leave> getLeaves(LeaveCri cri) throws SQLException{
		String l_cri = " (leaveID like '%" + cri.getKeyword() + "%'"
						+"or comment like '%"+cri.getKeyword()+"%'"
						+"or dissemination like '%" + cri.getKeyword() + "%'"
						+"or employeeID like '%" + cri.getKeyword() + "%'"  
						+"or date_format(endDate, '%d/%m/%Y') like '%" + cri.getKeyword() + "%'" 
						+"or leaveCategory like '%" + cri.getKeyword() + "%'" 
						+"or reason like '%" + cri.getKeyword() + "%'"
						+"or date_format(startDate, '%d/%m/%Y') like '%" + cri.getKeyword() + "%'"
						+"or supervisorID like '%" + cri.getKeyword() + "%')";
		if(!cri.getEmployeID().equals(""))
			l_cri += " and employeeid = '" + cri.getEmployeID() + "'";
		if(!cri.getSupervisorID().equals(""))
			l_cri += " and supervisorid = '" + cri.getSupervisorID() + "'";
		if(cri.getLeaveStatus() != 0)
			l_cri += " and leavestatus = " + cri.getLeaveStatus();
		query = getSelectQuery()
				+ " where leavestatus <> " + LeaveStatus.delete 
				+ " and " + l_cri 
				+ " order by leaveid desc"
				+ " limit " + cri.getRowIndex() + ", " + cri.getRowLimit();
		
            jdbcTemplate = new JdbcTemplate(dataSource);
		
		String l_queryCount = "select count(leaveID) from " + table + " where " + l_cri;
		cri.setRecordCount(jdbcTemplate.queryForLong(l_queryCount));
		
		return jdbcTemplate.query(query, this);
	}

	@Override
	public boolean updateLeaveStatus(long leaveID, byte status) throws SQLException {
		query = "update " + table + " set leavestatus = " + status + " where leaveid = " + leaveID;
		jdbcTemplate = new JdbcTemplate(dataSource);
		return jdbcTemplate.update(query) > 0;
	}

	@Override
	public boolean updateLeaveStatus(long leaveID, byte status, String comment)
			throws SQLException {
		query = "update " + table + " set leavestatus = ?, comment = ? where leaveid = " + leaveID;
		jdbcTemplate = new JdbcTemplate(dataSource);
		return jdbcTemplate.update(query, status, comment) > 0;
	}

	@Override
	public List<Leave> getLeavesByDate(String date, String empID) throws SQLException {
		query = getSelectQuery() + " where startdate like '" + date + "%' and employeeid = '" + empID + "' and leavestatus <> " + LeaveStatus.delete;
		jdbcTemplate = new JdbcTemplate(dataSource);
		return jdbcTemplate.query(query, this);
	}

	@Override
	public int getLeavesCountByDate(String date, String empID, String category)
			throws SQLException {
		query = "select count(leaveid) from " + table + " where startdate like '" + date + "%' and employeeid = '" + empID + "' and leavestatus = " + LeaveStatus.approve;
		if(!category.equals(""))
		{
			query += " and leavecategory = '" + category + "'";
		}
		jdbcTemplate = new JdbcTemplate(dataSource);
		return jdbcTemplate.queryForInt(query);
	}

	@Override
	public boolean isApprovedDate(String date, String empID) throws SQLException {
		query = "select count(leaveid) from " + table + " where startdate <= '" + date + "' and enddate >= '" + date + "' and employeeid = '" + empID + "' and leavestatus = " + LeaveStatus.approve;
		jdbcTemplate = new JdbcTemplate(dataSource);
		return jdbcTemplate.queryForInt(query) > 0;
	}

	@Override
	public List<Leave> prepareReport(LeaveCri cri) throws SQLException {
		String l_cri = "";
		if(cri.getLeaveStatus() != 0)
			l_cri = " and leavestatus = " + cri.getLeaveStatus();
		if(!cri.getLeaveCategory().equals(""))
			l_cri = " and leavecategory = '" + cri.getLeaveCategory() + "'";
		query = getSelectQuery() 
				+ " where leavestatus <> " + LeaveStatus.delete
				+ " and startdate >= '" + cri.getStartDate() + "'"
				+ " and startdate <= '" + cri.getEndDate() + "'"
				+ l_cri;
		jdbcTemplate = new JdbcTemplate(dataSource);
		return jdbcTemplate.query(query, this);
	}
}
